
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/14/2014 00:00:34
-- Generated from EDMX file: C:\Users\ChaouenCrea\Desktop\RAMED\RAMED.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [RAMED];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK__Annexe_ad__id_ce__1B0907CE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Annexe_administratif] DROP CONSTRAINT [FK__Annexe_ad__id_ce__1B0907CE];
GO
IF OBJECT_ID(N'[dbo].[FK__Beneficai__id_ce__09DE7BCC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Beneficaire] DROP CONSTRAINT [FK__Beneficai__id_ce__09DE7BCC];
GO
IF OBJECT_ID(N'[dbo].[FK__Beneficai__id_pv__08EA5793]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Beneficaire] DROP CONSTRAINT [FK__Beneficai__id_pv__08EA5793];
GO
IF OBJECT_ID(N'[dbo].[FK__Bordereau__id_mo__1273C1CD]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Bordereau] DROP CONSTRAINT [FK__Bordereau__id_mo__1273C1CD];
GO
IF OBJECT_ID(N'[dbo].[FK__Demande_l__id_an__20C1E124]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Demande_litigieuses] DROP CONSTRAINT [FK__Demande_l__id_an__20C1E124];
GO
IF OBJECT_ID(N'[dbo].[FK__Dossier_i__id_an__2A4B4B5E]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Dossier_incomplet] DROP CONSTRAINT [FK__Dossier_i__id_an__2A4B4B5E];
GO
IF OBJECT_ID(N'[dbo].[FK__Page__id_pv__35BCFE0A]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Page] DROP CONSTRAINT [FK__Page__id_pv__35BCFE0A];
GO
IF OBJECT_ID(N'[dbo].[FK__Responsab__id_an__2F10007B]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Responsable] DROP CONSTRAINT [FK__Responsab__id_an__2F10007B];
GO
IF OBJECT_ID(N'[dbo].[FK__Traitemen__id_de__25869641]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Traitement] DROP CONSTRAINT [FK__Traitemen__id_de__25869641];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Annexe_administratif]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Annexe_administratif];
GO
IF OBJECT_ID(N'[dbo].[Beneficaire]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Beneficaire];
GO
IF OBJECT_ID(N'[dbo].[Bordereau]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Bordereau];
GO
IF OBJECT_ID(N'[dbo].[Centre_sante]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Centre_sante];
GO
IF OBJECT_ID(N'[dbo].[Cercle]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cercle];
GO
IF OBJECT_ID(N'[dbo].[Demande_litigieuses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Demande_litigieuses];
GO
IF OBJECT_ID(N'[dbo].[Dossier_incomplet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Dossier_incomplet];
GO
IF OBJECT_ID(N'[dbo].[Lot]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Lot];
GO
IF OBJECT_ID(N'[dbo].[Page]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Page];
GO
IF OBJECT_ID(N'[dbo].[Pv]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Pv];
GO
IF OBJECT_ID(N'[dbo].[Responsable]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Responsable];
GO
IF OBJECT_ID(N'[dbo].[Traitement]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Traitement];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Annexe_administratif'
CREATE TABLE [dbo].[Annexe_administratif] (
    [id_annexe_administratif] int IDENTITY(1,1) NOT NULL,
    [nom_annexe_administratif] varchar(40)  NULL,
    [adresse_annexe_administratif] varchar(45)  NULL,
    [tele_annexe_administratif] varchar(40)  NULL,
    [id_cercle] int  NULL
);
GO

-- Creating table 'Beneficaires'
CREATE TABLE [dbo].[Beneficaires] (
    [id_beneficaire] int IDENTITY(1,1) NOT NULL,
    [nom_beneficaire] varchar(50)  NULL,
    [prenom_beneficaire] varchar(50)  NULL,
    [CIN_beneficaire] varchar(15)  NULL,
    [adresse_beneficaire] varchar(60)  NULL,
    [type_beneficaire] varchar(40)  NULL,
    [id_pv] int  NULL,
    [id_centre_sante] int  NULL
);
GO

-- Creating table 'Bordereaux'
CREATE TABLE [dbo].[Bordereaux] (
    [id_bordereau] int IDENTITY(1,1) NOT NULL,
    [date_bordereau] datetime  NULL,
    [type_bordereau] varchar(50)  NULL,
    [id_mot] int  NULL
);
GO

-- Creating table 'Centre_sante'
CREATE TABLE [dbo].[Centre_sante] (
    [id_centre_sante] int IDENTITY(1,1) NOT NULL,
    [nom_centre_sante] varchar(40)  NULL,
    [tel_centre_sante] varchar(40)  NULL,
    [adresse_centre_sante] varchar(40)  NULL
);
GO

-- Creating table 'Cercles'
CREATE TABLE [dbo].[Cercles] (
    [id_cercle] int IDENTITY(1,1) NOT NULL,
    [nom_cercle] varchar(40)  NULL,
    [nature_cercle] varchar(50)  NULL,
    [num_cercle] int  NULL
);
GO

-- Creating table 'Demande_litigieuses'
CREATE TABLE [dbo].[Demande_litigieuses] (
    [id_demande_litigieuses] int IDENTITY(1,1) NOT NULL,
    [nmu_doosier] int  NULL,
    [erreur] varchar(40)  NULL,
    [complet] float  NULL,
    [id_annexe_administratif] int  NULL
);
GO

-- Creating table 'Dossier_incomplet'
CREATE TABLE [dbo].[Dossier_incomplet] (
    [id_dossier_incomplet] int IDENTITY(1,1) NOT NULL,
    [champs_manquent] varchar(70)  NULL,
    [num_dossier] int  NULL,
    [id_annexe_administratif] int  NULL
);
GO

-- Creating table 'Lots'
CREATE TABLE [dbo].[Lots] (
    [id_lot] int IDENTITY(1,1) NOT NULL,
    [date_traitement] datetime  NULL,
    [num_de_lot] int  NULL,
    [total_do_lot] int  NULL
);
GO

-- Creating table 'Pages'
CREATE TABLE [dbo].[Pages] (
    [id_page] int IDENTITY(1,1) NOT NULL,
    [image_] varbinary(max)  NULL,
    [id_pv] int  NULL
);
GO

-- Creating table 'Pvs'
CREATE TABLE [dbo].[Pvs] (
    [id_pv] int IDENTITY(1,1) NOT NULL,
    [date_pv] datetime  NULL,
    [nombre_de_dossier] int  NULL,
    [nombre_de_personne_sont_en_pauvre] int  NULL,
    [nombre_de_personne_sont_en_vulnérabilité] int  NULL,
    [nombre_acceptaion_de_dossie] int  NULL,
    [id_annexe_administrative] nvarchar(max)  NOT NULL,
    [porperty] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Responsables'
CREATE TABLE [dbo].[Responsables] (
    [id_responsable] int IDENTITY(1,1) NOT NULL,
    [nom_prenom_responsable] varchar(60)  NULL,
    [tel_responsable] varchar(15)  NULL,
    [CIN_responsable] varchar(20)  NULL,
    [id_annexe_administratif] int  NULL
);
GO

-- Creating table 'Traitements'
CREATE TABLE [dbo].[Traitements] (
    [id_traitement] int IDENTITY(1,1) NOT NULL,
    [description_traitement] varchar(60)  NULL,
    [date_traitement] datetime  NULL,
    [id_demande_litigieuses] int  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id_annexe_administratif] in table 'Annexe_administratif'
ALTER TABLE [dbo].[Annexe_administratif]
ADD CONSTRAINT [PK_Annexe_administratif]
    PRIMARY KEY CLUSTERED ([id_annexe_administratif] ASC);
GO

-- Creating primary key on [id_beneficaire] in table 'Beneficaires'
ALTER TABLE [dbo].[Beneficaires]
ADD CONSTRAINT [PK_Beneficaires]
    PRIMARY KEY CLUSTERED ([id_beneficaire] ASC);
GO

-- Creating primary key on [id_bordereau] in table 'Bordereaux'
ALTER TABLE [dbo].[Bordereaux]
ADD CONSTRAINT [PK_Bordereaux]
    PRIMARY KEY CLUSTERED ([id_bordereau] ASC);
GO

-- Creating primary key on [id_centre_sante] in table 'Centre_sante'
ALTER TABLE [dbo].[Centre_sante]
ADD CONSTRAINT [PK_Centre_sante]
    PRIMARY KEY CLUSTERED ([id_centre_sante] ASC);
GO

-- Creating primary key on [id_cercle] in table 'Cercles'
ALTER TABLE [dbo].[Cercles]
ADD CONSTRAINT [PK_Cercles]
    PRIMARY KEY CLUSTERED ([id_cercle] ASC);
GO

-- Creating primary key on [id_demande_litigieuses] in table 'Demande_litigieuses'
ALTER TABLE [dbo].[Demande_litigieuses]
ADD CONSTRAINT [PK_Demande_litigieuses]
    PRIMARY KEY CLUSTERED ([id_demande_litigieuses] ASC);
GO

-- Creating primary key on [id_dossier_incomplet] in table 'Dossier_incomplet'
ALTER TABLE [dbo].[Dossier_incomplet]
ADD CONSTRAINT [PK_Dossier_incomplet]
    PRIMARY KEY CLUSTERED ([id_dossier_incomplet] ASC);
GO

-- Creating primary key on [id_lot] in table 'Lots'
ALTER TABLE [dbo].[Lots]
ADD CONSTRAINT [PK_Lots]
    PRIMARY KEY CLUSTERED ([id_lot] ASC);
GO

-- Creating primary key on [id_page] in table 'Pages'
ALTER TABLE [dbo].[Pages]
ADD CONSTRAINT [PK_Pages]
    PRIMARY KEY CLUSTERED ([id_page] ASC);
GO

-- Creating primary key on [id_pv] in table 'Pvs'
ALTER TABLE [dbo].[Pvs]
ADD CONSTRAINT [PK_Pvs]
    PRIMARY KEY CLUSTERED ([id_pv] ASC);
GO

-- Creating primary key on [id_responsable] in table 'Responsables'
ALTER TABLE [dbo].[Responsables]
ADD CONSTRAINT [PK_Responsables]
    PRIMARY KEY CLUSTERED ([id_responsable] ASC);
GO

-- Creating primary key on [id_traitement] in table 'Traitements'
ALTER TABLE [dbo].[Traitements]
ADD CONSTRAINT [PK_Traitements]
    PRIMARY KEY CLUSTERED ([id_traitement] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [id_cercle] in table 'Annexe_administratif'
ALTER TABLE [dbo].[Annexe_administratif]
ADD CONSTRAINT [FK__Annexe_ad__id_ce__1B0907CE]
    FOREIGN KEY ([id_cercle])
    REFERENCES [dbo].[Cercles]
        ([id_cercle])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Annexe_ad__id_ce__1B0907CE'
CREATE INDEX [IX_FK__Annexe_ad__id_ce__1B0907CE]
ON [dbo].[Annexe_administratif]
    ([id_cercle]);
GO

-- Creating foreign key on [id_annexe_administratif] in table 'Demande_litigieuses'
ALTER TABLE [dbo].[Demande_litigieuses]
ADD CONSTRAINT [FK__Demande_l__id_an__20C1E124]
    FOREIGN KEY ([id_annexe_administratif])
    REFERENCES [dbo].[Annexe_administratif]
        ([id_annexe_administratif])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Demande_l__id_an__20C1E124'
CREATE INDEX [IX_FK__Demande_l__id_an__20C1E124]
ON [dbo].[Demande_litigieuses]
    ([id_annexe_administratif]);
GO

-- Creating foreign key on [id_annexe_administratif] in table 'Dossier_incomplet'
ALTER TABLE [dbo].[Dossier_incomplet]
ADD CONSTRAINT [FK__Dossier_i__id_an__2A4B4B5E]
    FOREIGN KEY ([id_annexe_administratif])
    REFERENCES [dbo].[Annexe_administratif]
        ([id_annexe_administratif])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Dossier_i__id_an__2A4B4B5E'
CREATE INDEX [IX_FK__Dossier_i__id_an__2A4B4B5E]
ON [dbo].[Dossier_incomplet]
    ([id_annexe_administratif]);
GO

-- Creating foreign key on [id_annexe_administratif] in table 'Responsables'
ALTER TABLE [dbo].[Responsables]
ADD CONSTRAINT [FK__Responsab__id_an__2F10007B]
    FOREIGN KEY ([id_annexe_administratif])
    REFERENCES [dbo].[Annexe_administratif]
        ([id_annexe_administratif])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Responsab__id_an__2F10007B'
CREATE INDEX [IX_FK__Responsab__id_an__2F10007B]
ON [dbo].[Responsables]
    ([id_annexe_administratif]);
GO

-- Creating foreign key on [id_centre_sante] in table 'Beneficaires'
ALTER TABLE [dbo].[Beneficaires]
ADD CONSTRAINT [FK__Beneficai__id_ce__09DE7BCC]
    FOREIGN KEY ([id_centre_sante])
    REFERENCES [dbo].[Centre_sante]
        ([id_centre_sante])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Beneficai__id_ce__09DE7BCC'
CREATE INDEX [IX_FK__Beneficai__id_ce__09DE7BCC]
ON [dbo].[Beneficaires]
    ([id_centre_sante]);
GO

-- Creating foreign key on [id_pv] in table 'Beneficaires'
ALTER TABLE [dbo].[Beneficaires]
ADD CONSTRAINT [FK__Beneficai__id_pv__08EA5793]
    FOREIGN KEY ([id_pv])
    REFERENCES [dbo].[Pvs]
        ([id_pv])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Beneficai__id_pv__08EA5793'
CREATE INDEX [IX_FK__Beneficai__id_pv__08EA5793]
ON [dbo].[Beneficaires]
    ([id_pv]);
GO

-- Creating foreign key on [id_mot] in table 'Bordereaux'
ALTER TABLE [dbo].[Bordereaux]
ADD CONSTRAINT [FK__Bordereau__id_mo__1273C1CD]
    FOREIGN KEY ([id_mot])
    REFERENCES [dbo].[Lots]
        ([id_lot])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Bordereau__id_mo__1273C1CD'
CREATE INDEX [IX_FK__Bordereau__id_mo__1273C1CD]
ON [dbo].[Bordereaux]
    ([id_mot]);
GO

-- Creating foreign key on [id_demande_litigieuses] in table 'Traitements'
ALTER TABLE [dbo].[Traitements]
ADD CONSTRAINT [FK__Traitemen__id_de__25869641]
    FOREIGN KEY ([id_demande_litigieuses])
    REFERENCES [dbo].[Demande_litigieuses]
        ([id_demande_litigieuses])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Traitemen__id_de__25869641'
CREATE INDEX [IX_FK__Traitemen__id_de__25869641]
ON [dbo].[Traitements]
    ([id_demande_litigieuses]);
GO

-- Creating foreign key on [id_pv] in table 'Pages'
ALTER TABLE [dbo].[Pages]
ADD CONSTRAINT [FK__Page__id_pv__35BCFE0A]
    FOREIGN KEY ([id_pv])
    REFERENCES [dbo].[Pvs]
        ([id_pv])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK__Page__id_pv__35BCFE0A'
CREATE INDEX [IX_FK__Page__id_pv__35BCFE0A]
ON [dbo].[Pages]
    ([id_pv]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------